unit RelCompras;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, cxLookAndFeelPainters, StdCtrls, cxButtons, cxDropDownEdit,
  cxCurrencyEdit, cxLookupEdit, cxDBLookupEdit, cxDBLookupComboBox,
  cxLabel, cxTextEdit, cxMaskEdit, cxCalendar, cxControls, cxContainer,
  cxEdit, cxGroupBox, FMTBcd, SqlExpr, Provider, DB, DBClient, ppCtrls,
  ppVar, ppPrnabl, ppClass, ppBands, ppCache, ppDB, ppDBPipe, ppComm,
  ppRelatv, ppProd, ppReport;

type
  TfrmRelCompras = class(TForm)
    cxGroupBox2: TcxGroupBox;
    cxGroupBox1: TcxGroupBox;
    deFim: TcxDateEdit;
    deIni: TcxDateEdit;
    cxLabel1: TcxLabel;
    cxGroupBox5: TcxGroupBox;
    cbPessoa: TcxLookupComboBox;
    cxGroupBox3: TcxGroupBox;
    cbOperacao: TcxLookupComboBox;
    cxGroupBox4: TcxGroupBox;
    cxGroupBox6: TcxGroupBox;
    cbVisu: TcxComboBox;
    btnImprimir: TcxButton;
    cxButton4: TcxButton;
    dsOperacoes: TDataSource;
    cdsOperacoes: TClientDataSet;
    cdsOperacoesCodigo: TIntegerField;
    cdsOperacoesDescricao: TStringField;
    dspOperacoes: TDataSetProvider;
    sqlOperacoes: TSQLDataSet;
    sqlOperacoesCodigo: TIntegerField;
    sqlOperacoesDescricao: TStringField;
    sqlPessoas: TSQLDataSet;
    sqlPessoasCodigo: TIntegerField;
    sqlPessoasNome: TStringField;
    dspPessoas: TDataSetProvider;
    cdsPessoas: TClientDataSet;
    cdsPessoasCodigo: TIntegerField;
    cdsPessoasNome: TStringField;
    dsPessoas: TDataSource;
    dsRelCompras: TDataSource;
    cdsRelCompras: TClientDataSet;
    dspRelCompras: TDataSetProvider;
    sqlRelCompras: TSQLDataSet;
    ppRRelCompras: TppReport;
    ppDRelCompras: TppDBPipeline;
    ppHeaderBand1: TppHeaderBand;
    ppDetailBand1: TppDetailBand;
    ppFooterBand1: TppFooterBand;
    ppLabel1: TppLabel;
    ppSystemVariable1: TppSystemVariable;
    ppLine1: TppLine;
    ppLabel2: TppLabel;
    ppLabel3: TppLabel;
    ppDBText1: TppDBText;
    ppLabel4: TppLabel;
    ppDBText2: TppDBText;
    ppLabel5: TppLabel;
    ppDBText3: TppDBText;
    ppLine2: TppLine;
    ppLabel6: TppLabel;
    ppLabel7: TppLabel;
    ppLabel8: TppLabel;
    ppLabel9: TppLabel;
    ppLabel10: TppLabel;
    ppLabel11: TppLabel;
    ppLine4: TppLine;
    ppDBText4: TppDBText;
    ppDBText5: TppDBText;
    ppDBText6: TppDBText;
    ppDBText7: TppDBText;
    ppDBText8: TppDBText;
    ppDBText9: TppDBText;
    sqlRelComprascPeriodo: TStringField;
    sqlRelComprascPessoa: TStringField;
    sqlRelComprascOperacao: TStringField;
    sqlRelComprasSequencial: TIntegerField;
    sqlRelComprasNumero: TIntegerField;
    sqlRelComprasData: TSQLTimeStampField;
    sqlRelComprasOperacao: TStringField;
    sqlRelComprasNome: TStringField;
    sqlRelComprasValor: TFMTBCDField;
    cdsRelComprascPeriodo: TStringField;
    cdsRelComprascPessoa: TStringField;
    cdsRelComprascOperacao: TStringField;
    cdsRelComprasSequencial: TIntegerField;
    cdsRelComprasNumero: TIntegerField;
    cdsRelComprasData: TSQLTimeStampField;
    cdsRelComprasOperacao: TStringField;
    cdsRelComprasNome: TStringField;
    cdsRelComprasValor: TFMTBCDField;
    dsRelComprasItens: TDataSource;
    cdsRelComprasItens: TClientDataSet;
    dspRelComprasItens: TDataSetProvider;
    sqlRelComprasItens: TSQLDataSet;
    ppRRelComprasItens: TppReport;
    ppHeaderBand2: TppHeaderBand;
    ppLabel12: TppLabel;
    ppSystemVariable2: TppSystemVariable;
    ppLine3: TppLine;
    ppLabel13: TppLabel;
    ppLabel14: TppLabel;
    ppDBText10: TppDBText;
    ppLabel15: TppLabel;
    ppDBText11: TppDBText;
    ppLabel16: TppLabel;
    ppDBText12: TppDBText;
    ppLine5: TppLine;
    ppLabel17: TppLabel;
    ppLabel18: TppLabel;
    ppLabel19: TppLabel;
    ppLabel20: TppLabel;
    ppLabel21: TppLabel;
    ppLabel22: TppLabel;
    ppLine6: TppLine;
    ppDetailBand2: TppDetailBand;
    ppDBText13: TppDBText;
    ppDBText14: TppDBText;
    ppDBText15: TppDBText;
    ppDBText16: TppDBText;
    ppDBText17: TppDBText;
    ppDBText18: TppDBText;
    ppFooterBand2: TppFooterBand;
    ppDRelComprasItens: TppDBPipeline;
    sqlRelComprasItenscPeriodo: TStringField;
    sqlRelComprasItenscPessoa: TStringField;
    sqlRelComprasItenscOperacao: TStringField;
    sqlRelComprasItensSequencial: TIntegerField;
    sqlRelComprasItensNumero: TIntegerField;
    sqlRelComprasItensData: TSQLTimeStampField;
    sqlRelComprasItensOperacao: TStringField;
    sqlRelComprasItensNome: TStringField;
    sqlRelComprasItensValor: TFMTBCDField;
    sqlRelComprasItensCodProd: TIntegerField;
    sqlRelComprasItensBarras: TStringField;
    sqlRelComprasItensDEscProd: TStringField;
    sqlRelComprasItensQtd: TFMTBCDField;
    sqlRelComprasItensValorUnit: TFMTBCDField;
    sqlRelComprasItensValorTotal: TFMTBCDField;
    sqlRelComprasItensCategoria: TStringField;
    cdsRelComprasItenscPeriodo: TStringField;
    cdsRelComprasItenscPessoa: TStringField;
    cdsRelComprasItenscOperacao: TStringField;
    cdsRelComprasItensSequencial: TIntegerField;
    cdsRelComprasItensNumero: TIntegerField;
    cdsRelComprasItensData: TSQLTimeStampField;
    cdsRelComprasItensOperacao: TStringField;
    cdsRelComprasItensNome: TStringField;
    cdsRelComprasItensValor: TFMTBCDField;
    cdsRelComprasItensCodProd: TIntegerField;
    cdsRelComprasItensBarras: TStringField;
    cdsRelComprasItensDEscProd: TStringField;
    cdsRelComprasItensQtd: TFMTBCDField;
    cdsRelComprasItensValorUnit: TFMTBCDField;
    cdsRelComprasItensValorTotal: TFMTBCDField;
    cdsRelComprasItensCategoria: TStringField;
    ppGroup1: TppGroup;
    ppGroupHeaderBand1: TppGroupHeaderBand;
    ppGroupFooterBand1: TppGroupFooterBand;
    ppLabel23: TppLabel;
    ppLabel24: TppLabel;
    ppLabel25: TppLabel;
    ppLabel26: TppLabel;
    ppLabel27: TppLabel;
    ppDBText19: TppDBText;
    ppDBText20: TppDBText;
    ppDBText21: TppDBText;
    ppDBText23: TppDBText;
    ppDBText24: TppDBText;
    ppDBText25: TppDBText;
    ppLine7: TppLine;
    ppLine8: TppLine;
    procedure AbreDataSets;
    procedure QueryCompras;
    procedure FormShow(Sender: TObject);
    procedure CarregaFiltros;
    procedure btnImprimirClick(Sender: TObject);
    procedure QueryComprasItens;
  private
    S:TStringList;
  public
    { Public declarations }
  end;

var
  frmRelCompras: TfrmRelCompras;

implementation
Uses M_Principal;
{$R *.dfm}

{ TfrmRelCompras }

procedure TfrmRelCompras.AbreDataSets;
begin
  cdsPessoas.Open;
  cdsOperacoes.Open;
end;

procedure TfrmRelCompras.QueryCompras;
begin
  S.Clear;
  S.Add('Select');
  CarregaFiltros;
  S.Add('C.Sequencial, C.Numero, C.Data, O.Descricao Operacao,');
  S.Add('P.Nome, C.Valor');
  S.Add('From Compras C');
  S.Add('Join Operacoes O on C.Operacao = O.Codigo');
  S.Add('Join Pessoas P on C.Pessoa = P.Codigo');
  S.Add('Where C.data >='+QuotedStr(FormatDateTime('mm/dd/yyyy',deINi.Date)));
  S.Add('And C.data <='+QuotedStr(FormatDateTime('mm/d/yyyy',deFim.Date)));
  if cbPessoa.EditValue > 0 Then
    S.Add('And C.Pessoa = ' + IntToStr(cbPessoa.EditValue));
  if cbOperacao.EditValue > 0 Then
    S.Add('And C.Operacao = ' + IntToStr(cbOperacao.EditValue));
  with cdsRelCompras do begin
    Close;
    CommandText:=S.text;
    Open;
  end;
  ppRRelCompras.Print;
end;

procedure TfrmRelCompras.FormShow(Sender: TObject);
begin
  AbreDataSets;
  deIni.Date:=Date-30;
  deFim.Date:=Date;  
  S:=TStringList.Create;
end;

procedure TfrmRelCompras.CarregaFiltros;
begin
  S.Add(QuotedStr(deIni.Text+' A ' +deFim.Text)+' as cPeriodo,');
  S.Add(QuotedStr(cbPessoa.Text)+' as cPessoa,');
  S.Add(QuotedStr(cbOperacao.Text)+' as cOperacao,');
end;

procedure TfrmRelCompras.btnImprimirClick(Sender: TObject);
begin
  if cbVisu.ItemIndex=0 then
    QueryCompras
  else if cbVisu.ItemIndex=1 then
    QueryComprasItens;
end;

procedure TfrmRelCompras.QueryComprasItens;
begin
  S.Clear;
  S.Add('Select');
  CarregaFiltros;
  S.Add('C.Sequencial, C.Numero, C.Data, O.Descricao Operacao,');
  S.Add('P.Nome, C.Valor,');
  S.Add('PR.Codigo CodProd, PR.Barras, PR.DEscricao DEscProd, I.Qtd, I.ValorUnit, I.ValorTotal,');
  S.Add('CA.Descricao Categoria');
  S.Add('From Compras C');
  S.Add('Join Operacoes O on C.Operacao = O.Codigo');
  S.Add('Join Pessoas P on C.Pessoa = P.Codigo');
  S.Add('Join ItensCompra I on C.Sequencial = I.SeqCompra');
  S.Add('Join Produtos PR on I.Produto = PR.Codigo');
  S.Add('Join Categorias CA on I.CodConfig = CA.Sequencial');
  S.Add('Where C.data >='+QuotedStr(FormatDateTime('mm/dd/yyyy',deINi.Date)));
  S.Add('And C.data <='+QuotedStr(FormatDateTime('mm/d/yyyy',deFim.Date)));
  if cbPessoa.EditValue > 0 Then
    S.Add('And C.Pessoa = ' + IntToStr(cbPessoa.EditValue));
  if cbOperacao.EditValue > 0 Then
    S.Add('And C.Operacao = ' + IntToStr(cbOperacao.EditValue));
  showmessage(S.text);
  with cdsRelComprasItens do begin
    Close;
    CommandText:=S.text;
    Open;
  end;
  ppRRelComprasItens.Print;
end;

end.
